# cat sql_helper.py
 
import pymysql
import threading
from dbutils.pooled_db import PooledDB, SharedDBConnection
import logging

dbinfo={
    "host":'127.0.0.1',
    "port":3306,
    "user":'root',
    "passwd":'111111',
    "db":'db_ybtool',
}

Pool1=None

def _initPoll():
    global dbinfo
    global Pool1
    if Pool1 is None:
        try:
            Pool1 = PooledDB(
                creator=pymysql,  # 使用链接数据库的模块
                maxconnections=20,  # 连接池允许的最大连接数，0和None表示不限制连接数
                mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
                maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
                #maxshared=3,  # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
                blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
                maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
                setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
                ping=0,
                # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
                host=dbinfo['host'],
                port=dbinfo['port'],
                user=dbinfo['user'],
                passwd=dbinfo['passwd'],
                db=dbinfo['db'],
                charset='utf8'
            )
        except Exception as err1:
            logging.error('数据库连接池初始化失败', err1)
            POOL1 = None
 
 
def connect():

    _initPoll()

    if Pool1 is None:
        logging.warning('数据库连接池isNone , 无法执行数据库链接')
        return
    # 创建连接
    conn = Pool1.connection()
    # 创建游标
    cursor = conn.cursor(pymysql.cursors.DictCursor)
 
    return conn,cursor
 
def close(conn,cursor):
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()
 
def fetch_one(sql,args):

    logging.debug(f"SqlHelper.fetch_one:: {sql}\n {args}")
    conn,cursor = connect()
    # 执行SQL，并返回收影响行数
    effect_row = cursor.execute(sql,args)
    result = cursor.fetchone()
    close(conn,cursor)
 
    return result
 
def fetch_all(sql,args):

    logging.debug(f"SqlHelper.fetch_all:: {sql}\n {args}")

    conn, cursor = connect()
 
    # 执行SQL，并返回收影响行数
    cursor.execute(sql,args)
    result = cursor.fetchall()
 
    close(conn, cursor)
    return result
 
def insert(sql,args):
    """
    创建数据
    :param sql: 含有占位符的SQL
    :return:
    """

    

    conn, cursor = connect()
 
    logging.debug(f"SqlHelper.insert:: {sql}\n {args}")

    # 执行SQL，并返回收影响行数
    effect_row = cursor.execute(sql,args)
    conn.commit()
 
    close(conn, cursor)
 
def delete(sql,args):
    """
    创建数据
    :param sql: 含有占位符的SQL
    :return:
    """
    conn, cursor = connect()

    logging.debug(f"SqlHelper.delete:: {sql}\n {args}")
 
    # 执行SQL，并返回收影响行数
    effect_row = cursor.execute(sql,args)
 
    conn.commit()
 
    close(conn, cursor)
 
    return effect_row
 
def update(sql,args):
    conn, cursor = connect()

    logging.debug(f"SqlHelper.update:: {sql}\n {args}")
 
    # 执行SQL，并返回收影响行数
    effect_row = cursor.execute(sql, args)
 
    conn.commit()
 
    close(conn, cursor)
 
    return effect_row

def batch_execute(arrSqlParam):
    conn, cursor = connect()

    effect_row = 0
    for sql,param in arrSqlParam:
        # 执行SQL，并返回收影响行数
        effect_row += cursor.execute(sql,param)
 
    conn.commit()
 
    close(conn, cursor)
 
    return effect_row